package com.monster.dao;

import com.monster.pojo.Album;
import com.monster.pojo.Singer;
import com.monster.pojo.Song;
import com.monster.util.PageUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AlbumDaoImpl extends BaseDao implements AlbumDao{
    /**
     * 查询全部专辑
     * @param page
     * @return album集合
     * @author lym
     */
    public ArrayList<Album> getAllAlbums(int page){
        Album album = null;
        Singer singer = null;
        ArrayList<Album> alList = new ArrayList<Album>();
        String sql = "SELECT album.`album_id`,album_name, singer.singer_poster, singer.singer_id,singer_name, album_public_time , album_poster FROM album " +
                "JOIN singer_album ON  album.album_id = singer_album.`album_id` " +
                "JOIN singer ON singer_album.`singer_id` = singer.`singer_id` where 1 = 1 order by album_hot desc limit ?,30 ";
        Object[] params = {(page-1)*30};
        rs = this.executeQuery(sql, params);
        try {
            while (rs.next()) {
                album = new Album();
                singer = new Singer();
                //专辑属性
                album.setAlbumId(rs.getLong("album_id"));
                album.setAlbumName(rs.getString("album_name"));
                album.setAlbumPublicTime(rs.getString("album_public_time"));
                album.setAlbumPoster(rs.getString("album.album_poster"));
               // album.setAlbumProfile(rs.getString("album.album_profile"));
                //歌手属性
                singer.setSingerName(rs.getString("singer_name"));
                singer.setSingerId(rs.getLong("singer.singer_id"));
                singer.setPoster(rs.getString("singer.singer_poster"));
                //singer.setProfile(rs.getString("singer.singer_profile"));

                album.setSinger(singer);
                alList.add(album);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        PageUtil.params=null;
        PageUtil.sql=sql;
        return alList;
    }

    @Override
    //根据专辑id拿到歌曲id的集合
    public ArrayList<Long> findSongIdbyalbumId(Long id ) {
        String sql = "select song_id from song where album_id = ? " ;
        Object [] obj = {id};
        rs  = this.executeQuery(sql,obj);
        ArrayList<Long> list = new ArrayList<Long>();
        try {
            while(rs.next()){
                Long i = rs.getLong(1);
                list.add(i);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    //根据专辑id查询专辑
    public Album findSongsbyAlbumId(Long id) {
        String sql = "select * from album where album_id = ?";
        Object [] obj = {id};
        rs = this.executeQuery(sql,obj);
        Album album = new Album();
        try {
            while(rs.next()){
                album.setAlbumId(rs.getLong(1));//专辑ID
                album.setAlbumName(rs.getString(2));//专辑名字
                album.setAlbumGenre(rs.getString(3));//专辑流派
                album.setAlbumProfile(rs.getString(4));//专辑简介
                album.setAlbumLanguage(rs.getString(5));//专辑语种
                album.setAlbumPublicTime(rs.getString(6));//发行时间
                album.setAlbumPoster(rs.getString(7));//专辑海报
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return album;
    }

    @Override
    //根据专辑流派拿到专辑集合
    /**
     * 根据筛选组合查询
     * @param language
     * @param genre
     * @param page
     * @param orderType
     * @return ArrayList<Album>
     */
    public ArrayList<Album> findAlbumbyAlbumLanguageGenre(String language, String genre , String orderType,int page) {
        Singer singer = null;
        Album album = null;
        ArrayList<Album> alList = new ArrayList<Album>();
        ArrayList<Object> oList = new ArrayList<Object>();
        String sql = "SELECT album.`album_id`,album_name, singer.singer_poster, singer.singer_id,singer_name, album_public_time , album_poster FROM album " +
                "JOIN singer_album ON  album.album_id = singer_album.`album_id` " +
                "JOIN singer ON singer_album.`singer_id` = singer.`singer_id` where 1 = 1";
        if(!(language.equals("null"))&&language!=null){
            sql += " and album.album_language = ? ";
            oList.add(language);
        }
        if(!(genre.equals("null"))&&genre!=null){
            sql += " and album.album_genre = ? ";
            oList.add(genre);
        }
        System.out.println(genre);
        if(orderType!=null){
            if(orderType.equals("最新")){
                sql += " order by album_public_time desc ";
            }else if(orderType.equals("最热")){
                sql += " order by album_hot desc ";
            }
        }
        if(page>0){
            sql += " limit ?,30";
            oList.add((page-1)*30);
        }
        Object[] params = new Object[oList.size()];
        for(int i =0;i<oList.size();i++){
            params[i]=oList.get(i);
        }
        rs = this.executeQuery(sql,params);
        try {
            while(rs.next()){
                album = new Album();
                singer = new Singer();
                //专辑属性
                album.setAlbumId(rs.getLong("album.album_id"));
                album.setAlbumName(rs.getString("album.album_name"));
                album.setAlbumPublicTime(rs.getString("album_public_time"));
                album.setAlbumPoster(rs.getString("album.album_poster"));

                //歌手属性
                singer.setSingerName(rs.getString("singer.singer_name"));
                singer.setSingerId(rs.getLong("singer.singer_id"));
                singer.setPoster(rs.getString("singer.singer_poster"));

                album.setSinger(singer);
                alList.add(album);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(!(language.equals("null"))&&language!=null){
            PageUtil.params= new Object[]{language};
        }else if((!(language.equals("null"))&&language!=null)&&(!(genre.equals("null"))&&genre!=null)){
            PageUtil.params= new Object[]{language,genre};
        }
        PageUtil.sql=sql;
        return alList;

    }

    @Override
    //模糊查询专辑名
    public ArrayList<Album> findAlbumbyAlbumname(String name) {
        String sql = "SELECT * FROM album LEFT JOIN singer_album  ON singer_album.album_id=album.album_id \n" +
                "LEFT JOIN singer  ON singer.singer_id=singer_album.singer_id \n" +
                "WHERE album_name LIKE '%"+ name +"%' \n" +
                "ORDER BY album_hot DESC ";
        Object [] obj = null;
        rs = this.executeQuery(sql , obj);
        ArrayList<Album> list = new ArrayList<Album>();
        try {
            while(rs.next()){
                Album album = new Album();
                Singer singer = new Singer();
                album.setAlbumId(rs.getLong("album.album_id"));
                album.setAlbumName(rs.getString("album.album_name"));
                album.setAlbumPoster(rs.getString("album.album_poster"));
                singer.setSingerId(rs.getLong("singer.singer_id"));
                singer.setSingerName(rs.getString("singer.singer_name"));
                album.setSinger(singer);
                list.add(album);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    /**
     * 根据专辑Id查询曲目数
     * @param albumId
     * @return 专辑曲目数
     */
    @Override
    public int findSongCountByAlbumId(Long albumId) {
        String sql = "SELECT COUNT(1) FROM song WHERE album_id=?";
        Object [] obj = {albumId};
        rs = this.executeQuery(sql , obj);
        int result = 0;
        try {
            while(rs.next()){
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 根据专辑id查询歌手信息
     * @param albumId
     * @return 歌手id，歌手name
     */
    @Override
    public ArrayList<Singer> findSingersByAlbumId(Long albumId) {
        String sql = "SELECT s.singer_id,singer_name FROM singer s,singer_album sa WHERE s.singer_id=sa.singer_id AND album_id=?";
        Object [] obj = {albumId};
        rs = this.executeQuery(sql , obj);
        ArrayList<Singer> singers = new ArrayList<>();
        try {
            while(rs.next()){
                Singer s = new Singer();
                s.setSingerId(rs.getLong(1));
                s.setSingerName(rs.getString(2));
                singers.add(s);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return singers;
    }

    @Override
    //根据专辑流派拿到专辑集合
    public ArrayList<Album> findAlbumbyAlbumGenre(String genre , int page) {
        String sql = "select * from album where album_genre = ? ORDER BY song_public_time limit ?,20";
        Object [] obj = {genre,(page-1)*20};
        rs = this.executeQuery(sql,obj);
        ArrayList<Album> list = new ArrayList<Album>();
        try {
            while(rs.next()){
                Album album = new Album();
                album.setAlbumId(rs.getLong(1));
                album.setAlbumName(rs.getString(2));
                album.setAlbumGenre(rs.getString(3));
                album.setAlbumProfile(rs.getString(4));
                album.setAlbumLanguage(rs.getString(5));
                album.setAlbumPublicTime(rs.getString(6));
                album.setAlbumPoster(rs.getString(7));
                list.add(album);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 通过专辑ID拿到专辑歌曲集合
     */
    public List<Song> selectSongByAlbumId(Long albumId){
        List<Song> list = new ArrayList<Song>();
        //调用通过专辑ID拿到歌曲ID集合
        AlbumDao album = new AlbumDaoImpl();
        ArrayList<Long> songIdList = album.findSongIdbyalbumId(albumId);
        for(Long newSongIdList:songIdList){
            SongDao songDao = new SongDaoImpl();
            Song song = songDao.getSongById(newSongIdList);
            list.add(song);
        }
        return list;
    }
}
